package com.zxy.ziems.server.statistics.service.impl;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.Query;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zxy.btp.common.exception.ConditionException;
import com.zxy.ziems.common.pojo.MeasureDataDayBean;
import com.zxy.ziems.common.pojo.collector.DeviceProperty;
import com.zxy.ziems.server.classify.pojo.entity.Classify;
import com.zxy.ziems.server.classify.pojo.vo.ClassifyVO;
import com.zxy.ziems.server.classify.service.ClassifyService;
import com.zxy.ziems.server.device.mapper.DeviceMapper;
import com.zxy.ziems.server.device.mapper.DevicePropertyMapper;
import com.zxy.ziems.server.device.pojo.entity.DeviceEntity;
import com.zxy.ziems.server.domain.DeviceMeasureDataIncrementHour;
import com.zxy.ziems.server.energy.mapper.DeviceMeasureDataDayMapper;
import com.zxy.ziems.server.energy.mapper.DeviceMeasureDataHistoryMapper;
import com.zxy.ziems.server.energy.pojo.entity.DeviceMeasureDataDayEntity;
import com.zxy.ziems.server.energy.pojo.entity.DeviceMeasureDataHistoryEntity;
import com.zxy.ziems.server.energy.service.EnergyPriceService;
import com.zxy.ziems.server.mapper.ExhausGasChemicalMapper;
import com.zxy.ziems.server.mapper.ExhausGasChemicalRelMapper;
import com.zxy.ziems.server.mapper.GasConsumptionMapper;
import com.zxy.ziems.server.mapper.GasConsumptionRelMapper;
import com.zxy.ziems.server.service.DictItemService;
import com.zxy.ziems.server.statistics.pojo.dto.ExhaustGasChemicalsDto;
import com.zxy.ziems.server.statistics.pojo.dto.GasConsumptionDto;
import com.zxy.ziems.server.statistics.pojo.entity.ExhaustGasChemicalsData;
import com.zxy.ziems.server.statistics.pojo.entity.ExhaustGasChemicalsRelData;
import com.zxy.ziems.server.statistics.pojo.entity.GasConsumptionData;
import com.zxy.ziems.server.statistics.pojo.entity.GasConsumptionRelData;
import com.zxy.ziems.server.statistics.pojo.param.ExhausGasChemicalQueryParam;
import com.zxy.ziems.server.statistics.pojo.param.GasConsumptionQueryParam;
import com.zxy.ziems.server.statistics.pojo.vo.ValueLabelVo;
import com.zxy.ziems.server.statistics.service.ExhausGasChemicalService;
import com.zxy.ziems.server.statistics.service.GasConsumptionService;
import com.zxy.ziems.server.storage.service.MeasureDataDayStorageService;
import com.zxy.ziems.server.utils.IdUtil;
import com.zxy.ziems.server.utils.StringUtils;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class GasConsumptionServiceImpl implements GasConsumptionService {

    @Autowired
    private DictItemService dictItemService;
    @Autowired
    private ClassifyService classifyService;

    @Autowired
    private GasConsumptionMapper gasConsumptionMapper;

    @Autowired
    private GasConsumptionRelMapper gasConsumptionRelMapper;

    @Autowired
    private DevicePropertyMapper devicePropertyMapper;
    @Autowired
    private DeviceMapper deviceMapper;
    @Autowired
    private MeasureDataDayStorageService measureDataDayStorageService;
    @Autowired
    private DeviceMeasureDataDayMapper deviceMeasureDataDayMapper;
    @Autowired
    private DeviceMeasureDataHistoryMapper deviceMeasureDataHistoryMapper;
    @Autowired
    private EnergyPriceService energyPriceService;



    @Override
    public Map<String, ValueLabelVo> buildHeader(String parentClassifyId) {
        Map<String, ValueLabelVo> map = new LinkedHashMap<>();
        List<ClassifyVO> byParentClassifyId = classifyService.getByParentClassifyId(parentClassifyId);
        //公共部分
        ValueLabelVo date = new ValueLabelVo();
        date.setLabel("日期");
        date.setValue("date");
        map.put("date", date);

        List<String> sum = new ArrayList<>();
        for (ClassifyVO classifyVO : byParentClassifyId) {
            ValueLabelVo bodyValueLabel = new ValueLabelVo();
            bodyValueLabel.setLabel(classifyVO.getClassifyName());
            bodyValueLabel.setValue(classifyVO.getClassifyId());
            List<ValueLabelVo> childrens = new ArrayList<>();

            ValueLabelVo children01 = new ValueLabelVo();
            children01.setLabel("总量m³");
            children01.setValue(classifyVO.getClassifyId() + "N01");
            children01.setTarget(classifyVO.getClassifyId() + "N03");
            childrens.add(children01);

            ValueLabelVo children02 = new ValueLabelVo();
            children02.setLabel("剩余m³");
            children02.setValue(classifyVO.getClassifyId() + "N02");
            childrens.add(children02);

            ValueLabelVo children03 = new ValueLabelVo();
            children03.setLabel("日用量m³");
            children03.setValue(classifyVO.getClassifyId() + "N03");
            children03.setFlag(1);
            childrens.add(children03);
            sum.add(classifyVO.getClassifyId() + "N03");

            bodyValueLabel.setChildren(childrens);
            map.put(classifyVO.getClassifyId(), bodyValueLabel);
        }


        ValueLabelVo registrationTime = new ValueLabelVo();
        registrationTime.setLabel("登表时间");
        registrationTime.setValue("registrationTime");
        registrationTime.setTime(1);
        map.put("registrationTime", registrationTime);

        ValueLabelVo dailyConsumption = new ValueLabelVo();
        dailyConsumption.setLabel("每日用量");
        dailyConsumption.setValue("dailyConsumption");
        dailyConsumption.setSum(sum);
        map.put("dailyConsumption", dailyConsumption);

        return map;
    }

    @Override
    @Transactional
    public void addOrUpdate(GasConsumptionDto gasConsumptionDto, String userId) {
        LocalDateTime creteTime = LocalDateTime.now();
        LocalDateTime updateTime = creteTime;
        if (StringUtils.isNotEmpty(gasConsumptionDto.getId())) {
            //表示更新，先删除再更新
            GasConsumptionData gasConsumptionData = gasConsumptionMapper.selectById(gasConsumptionDto.getId());
            creteTime = gasConsumptionData.getCreateTime();
            gasConsumptionMapper.deleteById(gasConsumptionDto.getId());

            LambdaQueryWrapper<GasConsumptionRelData> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(GasConsumptionRelData::getPid, gasConsumptionData.getId());
            gasConsumptionRelMapper.delete(wrapper);

        }
        List<GasConsumptionData> gasConsumptionDataList = new ArrayList<>();
        List<GasConsumptionRelData> gasConsumptionRelDataList = new ArrayList<>();
        List<ClassifyVO> byParentClassifyId = classifyService.getByParentClassifyId(gasConsumptionDto.getClassifyId());
        List<Map<String, String>> data = gasConsumptionDto.getData();
        List<MeasureDataDayBean> measureDataDayBeanList = new ArrayList<>();
        List<String> deviceCodes = new ArrayList<>();
        for (Map<String, String> datum : data) {
            //公共部分
            GasConsumptionData gasConsumptionData = new GasConsumptionData();
            gasConsumptionData.setId(IdUtil.nextId());
            gasConsumptionData.setType(gasConsumptionDto.getClassifyId());
            // 对日期进行正确性校验
            if (!isValidDate(datum.get("date"))){
                throw new ConditionException("日期不正确");
            }
            gasConsumptionData.setDate(datum.get("date"));
            gasConsumptionData.setRegistrationTime(datum.getOrDefault("registrationTime", "00:00"));
            gasConsumptionData.setDailyConsumption(datum.get("dailyConsumption"));
            gasConsumptionData.setCreateTime(creteTime);
            gasConsumptionData.setUpdateTime(updateTime);
            gasConsumptionData.setCreateUserId(userId);
            gasConsumptionData.setUpdateUserId(userId);
            for (ClassifyVO classifyVO : byParentClassifyId) {
                GasConsumptionRelData gasConsumptionRelData = new GasConsumptionRelData();
                MeasureDataDayBean measureDataDayBean = new MeasureDataDayBean();
                String classifyId = classifyVO.getClassifyId();
                String total = datum.get(classifyId + "N01");
                String surplus = datum.get(classifyId + "N02");
                String dailyConsumption = datum.get(classifyId + "N03");
                gasConsumptionRelData.setId(IdUtil.nextId());
                gasConsumptionRelData.setPid(gasConsumptionData.getId());
                gasConsumptionRelData.setType(classifyVO.getClassifyId());
                gasConsumptionRelData.setDeviceType(classifyVO.getClassifyName());
                gasConsumptionRelData.setTotal(total);
                gasConsumptionRelData.setSurplus(surplus);
                gasConsumptionRelData.setDailyConsumption(dailyConsumption);
                gasConsumptionRelData.setCreateTime(creteTime);
                gasConsumptionRelData.setUpdateTime(updateTime);
                gasConsumptionRelData.setCreateUserId(userId);
                gasConsumptionRelData.setUpdateUserId(userId);
                gasConsumptionRelDataList.add(gasConsumptionRelData);

                deviceCodes.add(classifyId);
                measureDataDayBean.setDeviceId(getDeviceId(classifyId));
                measureDataDayBean.setMeasureValue(total);
                measureDataDayBean.setMeasureDate(LocalDate.parse(datum.get("date")).minusDays(1));
                measureDataDayBean.setMeasureTime(LocalDateTime.parse(datum.get("date")+"T"+datum.get("registrationTime")));
                measureDataDayBean.setIncrementValue(dailyConsumption);
                measureDataDayBeanList.add(measureDataDayBean);
            }
            gasConsumptionDataList.add(gasConsumptionData);
        }
        gasConsumptionMapper.batchAdd(gasConsumptionDataList);
        gasConsumptionRelMapper.batchAdd(gasConsumptionRelDataList);

        List<MeasureDataDayBean> deviceInfoList = devicePropertyMapper.selectDeviceInfo(deviceCodes);
        saveMeasureData(measureDataDayBeanList,deviceInfoList);
    }

    @Override
    public Page<GasConsumptionData> selectPage(GasConsumptionQueryParam param) {
        List<Map<String, String>> res = new ArrayList<>();
        LambdaQueryWrapper<GasConsumptionData> wrapper01 = new LambdaQueryWrapper<>();
        wrapper01.eq(GasConsumptionData::getType, param.getClassifyId())
                .orderByDesc(GasConsumptionData::getDate)
                .orderByDesc(GasConsumptionData::getCreateTime);
        Page<GasConsumptionData> page = new Page<>(param.getPageNo(), param.getPageSize());
        Page<GasConsumptionData> gasConsumptionDataPage = gasConsumptionMapper.selectPage(page, wrapper01);
        gasConsumptionDataPage.convert(x -> {
            Map<String, String> map = new HashMap<>();
            //公共数据
            map.put("id", x.getId());
            map.put("date", x.getDate());
            map.put("registrationTime", x.getRegistrationTime());
            map.put("dailyConsumption", x.getDailyConsumption());
            //填充数据
            LambdaQueryWrapper<GasConsumptionRelData> wrapper02 = new LambdaQueryWrapper<>();
            wrapper02.eq(GasConsumptionRelData::getPid, x.getId());
            List<GasConsumptionRelData> list = gasConsumptionRelMapper.selectList(wrapper02);
            list.stream().forEach(y -> {
                map.put(y.getType() + "N01", y.getTotal());
                map.put(y.getType() + "N02", y.getSurplus());
                map.put(y.getType() + "N03", y.getDailyConsumption());
                map.put("deviceType", y.getDeviceType());
            });
            return map;
        });
        return gasConsumptionDataPage;
    }

    public void exportTemplate(HttpServletResponse response) {
        Workbook wb = new XSSFWorkbook();
        CellStyle style = styleExcel(wb);
        CellStyle styleDate = styleDateExcel(wb);
        List<Classify> exhausGasClassifys = classifyService.classifysByDescription("天然气每日用量_");
        for (Classify classify : exhausGasClassifys) {
            Sheet sheet = wb.createSheet(classify.getClassifyName());
            List<ClassifyVO> childs = classifyService.getByParentClassifyId(classify.getClassifyId());
            int colSum = childs.size()*3 + 3;
            for (int i = 0; i < colSum; i++) {
                if (i == 0){
                    sheet.setColumnWidth(i, 15 * 256);
                }else {
                    sheet.setColumnWidth(i, 12 * 256);
                }
            }
            //第一行
            Row row0 = sheet.createRow(0);
            Cell cell00 = row0.createCell(0);
            cell00.setCellValue(LocalDate.now().getYear()+"年天然气使用记录");
            CellStyle titleStyle = titleStyleExcel(wb);
            cell00.setCellStyle(titleStyle);
            CellRangeAddress regionRow0 = new CellRangeAddress(0, 0, 0, colSum-1);
            sheet.addMergedRegion(regionRow0);

            //第二行
            Row row01 = sheet.createRow(1);
            Cell cell010 = row01.createCell(0);
            cell010.setCellValue("设备类型");
            cell010.setCellStyle(style);
            for (int i = 0; i < childs.size(); i++) {
                Cell cell = row01.createCell(3*i+1);
                cell.setCellValue(childs.get(i).getClassifyName());
                cell.setCellStyle(style);
            }
            Cell row01RegistrationTime = row01.createCell(childs.size()*3+1);
            row01RegistrationTime.setCellValue("登表时间");
            row01RegistrationTime.setCellStyle(style);
            Cell row01dailyConsumption = row01.createCell(childs.size()*3+2);
            row01dailyConsumption.setCellValue(classify.getClassifyName()+"每日用量");
            row01dailyConsumption.setCellStyle(style);

            for (int i = 0; i < childs.size(); i++) {
                CellRangeAddress regionRow2 = new CellRangeAddress(1, 1, i*3+1, i*3+3);
                sheet.addMergedRegion(regionRow2);
            }

            //第三行
            Row row02 = sheet.createRow(2);
            Cell cell020 = row02.createCell(0);
            cell020.setCellValue("日期");
            cell020.setCellStyle(style);
            for (int i = 0; i < childs.size(); i++) {
                Cell row02Total = row02.createCell(3*i+1);
                row02Total.setCellValue("总量m³");
                row02Total.setCellStyle(style);
                Cell row02Surplus = row02.createCell(3*i+2);
                row02Surplus.setCellValue("剩余m³");
                row02Surplus.setCellStyle(style);
                Cell row02DailyConsumption = row02.createCell(3*i+3);
                row02DailyConsumption.setCellValue("日用量m³");
                row02DailyConsumption.setCellStyle(style);
            }
            Cell cell02End01 = row02.createCell(colSum-2);
            cell02End01.setCellStyle(style);
            Cell cell02End02 = row02.createCell(colSum-1);
            cell02End02.setCellStyle(style);

            CellRangeAddress regionRow1Row3Cell7 = new CellRangeAddress(1, 3, colSum-2, colSum-2);
            sheet.addMergedRegion(regionRow1Row3Cell7);

            CellRangeAddress regionRow1Row3Cell8 = new CellRangeAddress(1, 3, colSum-1, colSum-1);
            sheet.addMergedRegion(regionRow1Row3Cell8);

            CellRangeAddress regionRow2Row3Cell0 = new CellRangeAddress(2, 3, 0, 0);
            sheet.addMergedRegion(regionRow2Row3Cell0);

            for (int i = 0; i < childs.size() * 3; i++) {
                CellRangeAddress regionRow2To3 = new CellRangeAddress(2, 3, i+1, i+1);
                sheet.addMergedRegion(regionRow2To3);
            }

            //第五行
            Row row04 = sheet.createRow(4);
            Cell cell40 = row04.createCell(0);
            cell40.setCellValue(LocalDate.now().minusDays(1));
            cell40.setCellStyle(styleDate);

            for (int i = 0; i < childs.size(); i++) {
                Cell cell04Total = row04.createCell(3*i+1);
                cell04Total.setCellValue(542.3);
                cell04Total.setCellStyle(style);

                Cell cell04Surplus = row04.createCell(3*i+2);
                cell04Surplus.setCellValue(532.6);
                cell04Surplus.setCellStyle(style);

                Cell cell04DailyConsumption = row04.createCell(3*i+3);
                cell04DailyConsumption.setCellValue(0);
                cell04DailyConsumption.setCellStyle(style);
            }

            Cell cell4Surplus = row04.createCell(colSum-2);
            // 设置单元格格式为 h:mm
            CellStyle cellTimeStyle = styleTimeExcel(wb);
            cell4Surplus.setCellStyle(cellTimeStyle);
            cell4Surplus.setCellValue(0);
//            cell4Surplus.setCellType(CellType.NUMERIC);

            Cell cell4DailyConsumption	 = row04.createCell(colSum-1);
            cell4DailyConsumption.setCellStyle(style);

            String sumRow4FormulaString = "";
            for (int i = 0; i < childs.size(); i++) {
                String character = getCharacter(i,'D',3);
                sumRow4FormulaString += character+"5+";
            }
            cell4DailyConsumption.setCellFormula(sumRow4FormulaString.substring(0,sumRow4FormulaString.length()-1));


            //第六行
            Row row05 = sheet.createRow(5);
            Cell cell50 = row05.createCell(0);
            cell50.setCellValue(LocalDate.now());
            cell50.setCellStyle(styleDate);

            for (int i = 0; i < childs.size(); i++) {
                Cell cell05Total = row05.createCell(3*i+1);
                cell05Total.setCellValue(532.6);
                cell05Total.setCellStyle(style);

                Cell cell05Surplus = row05.createCell(3*i+2);
                cell05Surplus.setCellValue(522.8);
                cell05Surplus.setCellStyle(style);

                Cell cell05DailyConsumption = row05.createCell(3*i+3);
                cell05DailyConsumption.setCellStyle(style);
                String character = getCharacter(i,'B',3);
                cell05DailyConsumption.setCellFormula(character+"6-"+character+"5");
            }

            Cell cell5Surplus = row05.createCell(colSum-2);
            // 设置单元格格式为 h:mm
            cell5Surplus.setCellStyle(cellTimeStyle);
            cell5Surplus.setCellValue(0);
//            cell5Surplus.setCellType(CellType.NUMERIC);

            Cell cell5DailyConsumption = row05.createCell(colSum-1);
            cell5DailyConsumption.setCellStyle(style);

            String sumRow5FormulaString = "";
            for (int i = 0; i < childs.size(); i++) {
                String character = getCharacter(i,'D',3);
                sumRow5FormulaString += character+"6+";
            }
            cell5DailyConsumption.setCellFormula(sumRow5FormulaString.substring(0,sumRow5FormulaString.length()-1));

            initRegionCellList(sheet.getMergedRegions(), sheet);
        }

        try {
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            throw new ConditionException(e.getMessage());
        }
    }

    public void initRegionCellList(List<CellRangeAddress> regions, Sheet sheet) {
        for (CellRangeAddress region : regions) {
            // 使用RegionUtil类为合并后的单元格添加边框
            RegionUtil.setBorderBottom(BorderStyle.valueOf((short) 1), region, sheet); // 下边框
            RegionUtil.setBorderLeft(BorderStyle.valueOf((short) 1), region, sheet); // 左边框
            RegionUtil.setBorderRight(BorderStyle.valueOf((short) 1), region, sheet); // 有边框
            RegionUtil.setBorderTop(BorderStyle.valueOf((short) 1), region, sheet); // 上边框
            RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), region, sheet); // 下边框
            RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), region, sheet); // 左边框
            RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), region, sheet); // 有边框
            RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), region, sheet); // 上边框
        }
    }

    public String getCharacter(int i,char startChar,int rate) {
        // 'B' 的 ASCII 码是 66，每增加一个索引，ASCII 码增加 rate
        // 返回值类型为 String
        return String.valueOf((char) (startChar + i * rate));
    }

    public CellStyle styleExcel(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        Font font = getFont(wb);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);// 设置水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        style.setWrapText(true);  // 换行
        // 设置边框样式
        style.setBorderTop(BorderStyle.THIN); // 上边框
        style.setBorderBottom(BorderStyle.THIN); // 下边框
        style.setBorderLeft(BorderStyle.THIN); // 左边框
        style.setBorderRight(BorderStyle.THIN); // 右边框

        // 设置边框颜色
        style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框颜色
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框颜色
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框颜色
        style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框颜色
        return style;
    }

    public CellStyle titleStyleExcel(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setColor(IndexedColors.BLACK.getIndex());
        font.setFontHeightInPoints((short) 22);
        font.setFontName("宋体");
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);// 设置水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        style.setWrapText(true);  // 换行
        // 设置边框样式
        style.setBorderTop(BorderStyle.THIN); // 上边框
        style.setBorderBottom(BorderStyle.THIN); // 下边框
        style.setBorderLeft(BorderStyle.THIN); // 左边框
        style.setBorderRight(BorderStyle.THIN); // 右边框

        // 设置边框颜色
        style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框颜色
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框颜色
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框颜色
        style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框颜色
        return style;
    }

    public Font getFont(Workbook wb){
        Font font = wb.createFont();
        font.setColor(IndexedColors.BLACK.getIndex());
        font.setFontHeightInPoints((short) 10);
        font.setFontName("宋体");
        return font;
    }

    public CellStyle styleDateExcel(Workbook wb){
        // 获取创建帮助器
        CreationHelper createHelper = wb.getCreationHelper();
        // 获取DataFormat对象
        DataFormat format = wb.createDataFormat();
        // 创建一个单元格样式并设置日期格式，例如："yyyy-mm-dd"
        short dateFormat = format.getFormat("yyyy-mm-dd");
        CellStyle styleDate = wb.createCellStyle();
        styleDate.setDataFormat(dateFormat);
        // 设置边框样式
        styleDate.setBorderTop(BorderStyle.THIN); // 上边框
        styleDate.setBorderBottom(BorderStyle.THIN); // 下边框
        styleDate.setBorderLeft(BorderStyle.THIN); // 左边框
        styleDate.setBorderRight(BorderStyle.THIN); // 右边框

        // 设置边框颜色
        styleDate.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框颜色
        styleDate.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框颜色
        styleDate.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框颜色
        styleDate.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框颜色

        Font font = getFont(wb);
        styleDate.setFont(font);
        styleDate.setAlignment(HorizontalAlignment.CENTER);// 设置水平居中
        styleDate.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        styleDate.setWrapText(true);  // 换行
        return styleDate;
    }

    public CellStyle styleTimeExcel(Workbook wb){
        // 获取DataFormat对象
        DataFormat format = wb.createDataFormat();
        // 创建一个单元格样式并设置日期格式，例如："yyyy-mm-dd"
        short dateFormat = format.getFormat("HH:mm");
        CellStyle styleDate = wb.createCellStyle();
        styleDate.setDataFormat(dateFormat);
        // 设置边框样式
        styleDate.setBorderTop(BorderStyle.THIN); // 上边框
        styleDate.setBorderBottom(BorderStyle.THIN); // 下边框
        styleDate.setBorderLeft(BorderStyle.THIN); // 左边框
        styleDate.setBorderRight(BorderStyle.THIN); // 右边框

        // 设置边框颜色
        styleDate.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框颜色
        styleDate.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框颜色
        styleDate.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框颜色
        styleDate.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框颜色

        Font font = getFont(wb);
        styleDate.setFont(font);
        styleDate.setAlignment(HorizontalAlignment.CENTER);// 设置水平居中
        styleDate.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        styleDate.setWrapText(true);  // 换行
        return styleDate;
    }

    @Override
    @Transactional
    public void importExcel(MultipartFile file, String userId) throws Exception {
        InputStream inputStream = file.getInputStream();
        Workbook workbook = new XSSFWorkbook(inputStream);
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            LocalDateTime now = LocalDateTime.now();
            List<GasConsumptionData> gasConsumptionDataList = new ArrayList<>();
            List<GasConsumptionRelData> gasConsumptionRelDataList = new ArrayList<>();
            // 同步数据到day和history表使用
            List<MeasureDataDayBean> measureDataDayBeanList = new ArrayList<>();
            Set<String> deviceCodes = new HashSet<>();
            Sheet sheet = workbook.getSheetAt(i);
            int lastRowNum = sheet.getLastRowNum();
            //先处理第二行表头
            String sheetName = sheet.getSheetName();
            Classify classify = classifyService.detailByDescription("天然气每日用量_" + sheetName);
            if (!Optional.ofNullable(classify).isPresent()) {
                throw new ConditionException("请正确填写天然气设备名称（表头）");
            }
            List<ClassifyVO> sortClassifyId = new ArrayList<>();
            List<ClassifyVO> byParentClassifyId = classifyService.getByParentClassifyId(classify.getClassifyId());
            //排下序，得跟导入的表格第二行顺序一致
            Row row02 = sheet.getRow(1);
            int row02CellNum = row02.getLastCellNum();
            for (int j = 1; j < row02CellNum - 2; j++) {
                for (ClassifyVO classifyVO : byParentClassifyId) {
                    if (classifyVO.getClassifyName().equals(row02.getCell(j).getStringCellValue())) {
                        sortClassifyId.add(classifyVO);
                    }
                }
            }
            //处理数据
            List<String>  date  = new ArrayList<>();
            for (int j = 4; j <= lastRowNum; j++) {
                //处理公共数据
                GasConsumptionData gasConsumptionData = new GasConsumptionData();
                gasConsumptionData.setId(IdUtil.nextId());
                gasConsumptionData.setType(classify.getClassifyId());
                gasConsumptionData.setCreateTime(now);
                gasConsumptionData.setUpdateTime(now);
                gasConsumptionData.setCreateUserId(userId);
                gasConsumptionData.setUpdateUserId(userId);
                Row row = sheet.getRow(j);
                short lastCellNum = row.getLastCellNum();
                for (int k = 0; k < lastCellNum; k++) {
                    Cell cell = row.getCell(k);
                    String cellValue = null;
                    if (k == 0) {
                        cellValue = getDateCellValue(cell);
                        gasConsumptionData.setDate(cellValue);
                        date.add(cellValue);
                    }  else if (k == lastCellNum - 2) {
                        cellValue = getTimeCellValue(cell);
                        gasConsumptionData.setRegistrationTime(cellValue);
                    } else if (k == lastCellNum - 1) {
                        cellValue = getCellValue(cell);
                        gasConsumptionData.setDailyConsumption(cellValue);
                    }
                }
                gasConsumptionDataList.add(gasConsumptionData);
                //有sortClassifyId.size()组数据
                int flag = 1;
                for (ClassifyVO classifyVO : sortClassifyId) {
                    GasConsumptionRelData gasConsumptionRelData = new GasConsumptionRelData();
                    MeasureDataDayBean measureDataDayBean = new MeasureDataDayBean();
                    Cell cellA = row.getCell(3 * flag - 2);
                    String total = getCellValue(cellA);
                    Cell cellB = row.getCell(3 * flag-1);
                    String surplus = getCellValue(cellB);
                    Cell cellC = row.getCell(3 * flag);
                    String dailyConsumption = getCellValue(cellC);
                    gasConsumptionRelData.setDeviceType(classifyVO.getClassifyName());
                    gasConsumptionRelData.setPid(gasConsumptionData.getId());
                    gasConsumptionRelData.setType(classifyVO.getClassifyId());
                    gasConsumptionRelData.setTotal(total);
                    gasConsumptionRelData.setSurplus(surplus);
                    gasConsumptionRelData.setDailyConsumption(dailyConsumption);
                    gasConsumptionRelData.setCreateTime(now);
                    gasConsumptionRelData.setUpdateTime(now);
                    gasConsumptionRelData.setCreateUserId(userId);
                    gasConsumptionRelData.setUpdateUserId(userId);
                    if(ObjectUtil.isNotEmpty(gasConsumptionRelData)
                            && StrUtil.isNotBlank(gasConsumptionRelData.getTotal())
                            && StrUtil.isNotBlank(gasConsumptionRelData.getDailyConsumption())){
                        gasConsumptionRelDataList.add(gasConsumptionRelData);
                    }else {
                        throw new ConditionException("总量或日用量为空");
                    }

                    if (ObjectUtil.isNotEmpty(gasConsumptionData) && StrUtil.isNotBlank(gasConsumptionData.getDate())){
                        deviceCodes.add(classifyVO.getClassifyId());
                        measureDataDayBean.setDeviceId(getDeviceId(classifyVO.getClassifyId()));
                        measureDataDayBean.setMeasureValue(total);
                        measureDataDayBean.setMeasureDate(LocalDate.parse(gasConsumptionData.getDate()).minusDays(1));
                        measureDataDayBean.setMeasureTime(LocalDateTime.parse(gasConsumptionData.getDate()+"T"+gasConsumptionData.getRegistrationTime()));
                        measureDataDayBean.setIncrementValue(dailyConsumption);
                        measureDataDayBeanList.add(measureDataDayBean);
                    }
                    flag++;
                }
            }

            LambdaQueryWrapper<GasConsumptionData> wrapper = new LambdaQueryWrapper<>();
            wrapper.select(GasConsumptionData::getId)
                    .eq(GasConsumptionData::getType, classify.getClassifyId())
                    .in(GasConsumptionData::getDate, date);
            List<String> repetitionId = gasConsumptionMapper.selectList(wrapper).stream()
                    .map(GasConsumptionData::getId).collect(Collectors.toList());

            if (!CollectionUtils.isEmpty(repetitionId)){
                //重复数据需要删除
                gasConsumptionMapper.batchDeletedById(repetitionId);
                gasConsumptionRelMapper.batchDeletedByPid(repetitionId);
            }

            if(!gasConsumptionDataList.isEmpty() && !gasConsumptionRelDataList.isEmpty()){
                gasConsumptionMapper.batchAdd(gasConsumptionDataList);
                gasConsumptionRelMapper.batchAdd(gasConsumptionRelDataList);
            }

            if(!deviceCodes.isEmpty()){
                List<String> deviceCodeList = deviceCodes.stream().toList();
                List<MeasureDataDayBean> deviceInfoList = devicePropertyMapper.selectDeviceInfo(deviceCodeList);
                if(deviceInfoList.isEmpty()){
                    throw new ConditionException("设备信息为空,未在系统中配置采集设备");
                }
                saveMeasureData(measureDataDayBeanList,deviceInfoList);
            }
        }
    }

    public List<ClassifyVO> getNeedClassifyId(List<ClassifyVO> byParentClassifyId) {
        List<ClassifyVO> res = new ArrayList<>();
        ClassifyVO classifyVOItem = new ClassifyVO();
        for (ClassifyVO classifyVO : byParentClassifyId) {
            BeanUtils.copyProperties(classifyVO, classifyVOItem);
            classifyVOItem.setRootClassifyId(classifyVO.getClassifyId() + "N01");
            res.add(classifyVOItem);
            classifyVOItem.setRootClassifyId(classifyVO.getClassifyId() + "N02");
            res.add(classifyVOItem);
        }
        return res;
    }

    private static String getCellValue(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return "";
        }
        switch (cell.getCellType()) {
            case NUMERIC:
                return cell.getNumericCellValue()+"";
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case BLANK:
                return "";
            case FORMULA:
                switch (cell.getCachedFormulaResultType()) {
                    case NUMERIC:
                        // 创建DecimalFormat对象，设置格式为保留3位小数
                        DecimalFormat decimalFormat = new DecimalFormat("0.000");
                        // 格式化数字
                        String formattedNumber = decimalFormat.format(cell.getNumericCellValue());
                        // 去掉末尾的0
                        formattedNumber = formattedNumber.replaceAll("0*$", "");
                        // 如果小数点后没有数字，则去掉小数点
                        formattedNumber = formattedNumber.replaceAll("\\.$", "");
                        return formattedNumber;
                    case STRING:
                        return cell.getStringCellValue();
                    case BOOLEAN:
                        return String.valueOf(cell.getBooleanCellValue());
                    case ERROR:
                        return "错误公式";
                    default:
                        return "0";
                }
            default:
                return "error";
        }
    }

    //处理日期类型-YYYY-MM-dd
    private static String getDateCellValue(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return "";
        }
        Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
        SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-dd");
        String formattedDate = sdf.format(date);
        if (!isValidDate(formattedDate)){
            throw new ConditionException("日期不正确");
        }
        return StringUtils.isEmpty(formattedDate) ? null : formattedDate;

    }

    //处理日期类型-YYYY-MM-dd
    private static String getTimeCellValue(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return "";
        }
        Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
        SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
        String formattedDate = sdf.format(date);
        return StringUtils.isEmpty(formattedDate) ? null : formattedDate;

    }
    // 验证日期是否正确
    public static boolean isValidDate(String dateStr) {
        try {
            if (dateStr.isBlank()){
                return false;
            }
            // 定义日期格式
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            // 尝试解析日期字符串
            LocalDate.parse(dateStr, formatter);
            return true;
        } catch (DateTimeParseException e) {
            // 若解析过程中抛出异常，说明日期不合法
            return false;
        }
    }


    /**
     * 删除指定日期的天然气数据
     * @param id
     */
    @Override
    @Transactional
    public void delete(String id) {
        // 根据id获得设备的date
        GasConsumptionData gasConsumptionData =  gasConsumptionMapper.selectById(id);
        // 获取设备code
        QueryWrapper<GasConsumptionRelData> gasConsumptionRelDataQueryWrapper = new QueryWrapper<>();
        gasConsumptionRelDataQueryWrapper.lambda()
                .eq(GasConsumptionRelData::getPid,id);
        List<String> deviceCodes = gasConsumptionRelMapper.selectList(gasConsumptionRelDataQueryWrapper).stream()
               .map(GasConsumptionRelData::getType).toList();
        // 获取设备id
        List<String> deviceIds = deviceMapper.batchGetDeviceId(deviceCodes);

        deviceMeasureDataDayMapper.batchDeleteByIdDate(deviceIds,gasConsumptionData.getDate());
        deviceMeasureDataHistoryMapper.batchDeleteByIdDate(deviceIds,gasConsumptionData.getDate());

        gasConsumptionMapper.deleteById(id);
        LambdaQueryWrapper<GasConsumptionRelData> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(GasConsumptionRelData::getPid, id);
        gasConsumptionRelMapper.delete(wrapper);
    }


    // 根据设备Code获得设备id
    private String getDeviceId(String deviceCode){
        QueryWrapper<DeviceEntity> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda()
                .eq(StrUtil.isNotBlank(deviceCode),DeviceEntity::getCode,deviceCode);
        DeviceEntity deviceEntity = deviceMapper.selectOne(queryWrapper);
        return deviceEntity.getId();
    }

    // 保存更新的值到day表和history表中
    private void saveMeasureData(List<MeasureDataDayBean> measureDataDayBeanList,List<MeasureDataDayBean> deviceInfoList) {
        Map<String,MeasureDataDayBean> measureDataMap = deviceInfoList.stream().collect(Collectors.toMap(MeasureDataDayBean::getDeviceId, x -> x));
        measureDataDayBeanList.stream().forEach(x -> {
            MeasureDataDayBean measureDataDayBean = measureDataMap.get(x.getDeviceId());
            if (measureDataDayBean != null){
                x.setDataTypeId(measureDataDayBean.getDataTypeId());
                x.setUnitId(measureDataDayBean.getUnitId());
                setDayPrice(x.getMeasureTime(),x.getDataTypeId(),x);
            }
        });
        // 打印measureDataDayBeanList中对象的属性
//        measureDataDayBeanList.forEach(x -> {
//            System.out.println("deviceId: " + x.getDeviceId()+" measureValue: " + x.getMeasureValue()+" measureTime: " + x.getMeasureTime());
//        });
        // 存储到表中
        measureDataDayStorageService.storageMeasureDataDayList(measureDataDayBeanList);
    }

    /**
     * 设置天级别的费用，根据dataType值进行设置费用
     * @param date
     * @param dataTypeId
     * @param dayBean
     */
    private void setDayPrice(LocalDateTime date, String dataTypeId, MeasureDataDayBean dayBean) {
        BigDecimal incrementValue = new BigDecimal(dayBean.getIncrementValue());
        BigDecimal price = energyPriceService.getEnergyPriceByDataTypeId(dataTypeId, date);
        if (price != null) {
            dayBean.setIncrementCost(incrementValue.multiply(price));
        }
    }

}
